# -- coding: utf-8 --
# @time : 2023/5/19
# @author : 周梦泽
# @file : keyword_merge.py
# @software: pycharm
# 此模块用于合成竞品词与关键词,并清洗数据
import pandas as pd


def merge_df(excel_path):
    gjc_hyc_data = pd.read_excel(excel_path, sheet_name="市场排行版词+延申词")
    competing_data = pd.read_excel(excel_path, sheet_name="竞品词", header=1)
    gjc_hyc_data_df = gjc_hyc_data.loc[:, ["关键词", "搜索人数"]]
    competing_data_df = competing_data.loc[:, ["关键词", "搜索人数"]]

    data_df = pd.concat([gjc_hyc_data_df, competing_data_df]).drop_duplicates()

    data_df = data_clear(data_df)
    data_df = data_df.sort_values('搜索人数', ascending=False)
    # 按照搜索人数降序排列
    with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:
        data_df.to_excel(writer, sheet_name='关键词+竞品词', index=False)


def data_clear(data: pd.DataFrame) -> pd.DataFrame:
    """
    清洗数据
    :param data: 补单周期表
    :return: 返回清洗后的补单周期表
    """
    data.dropna(axis=0, how='any', inplace=True)
    data.drop_duplicates(subset=['关键词'], keep='first', inplace=True)
    data_df = data.reset_index(drop=True)
    return data_df
